Java导出Excel模板，导出数据到指定模板，通过模板导入数据（一）

2023-03-26 07:15| 来源: 网络整理| 查看: 265

本文章主要是介绍阿里巴巴的easyexcel的使用

1. 首先需要我们导入easyexcel的依赖包

com.alibaba easyexcel 2.2.7

2. 前期工作准备

编写相关导出模板和导入模板。在项目的resources下创建文件夹，命名为excel

导出模板（此处仅做示例，字段根据自己项目来）：

导入模板（导入时需要哪些字段根据自己项目业务来订）：

将创建好的模板放置到创建好的resources下的excel文件夹内

3. 编写相关基础工具类

ExcelFillCellMergePrevColUtils.java

ExcelFillCellMergeStrategyUtils.java

import com.alibaba.excel.metadata.CellData; import com.alibaba.excel.metadata.Head; import com.alibaba.excel.write.handler.CellWriteHandler; import com.alibaba.excel.write.metadata.holder.WriteSheetHolder; import com.alibaba.excel.write.metadata.holder.WriteTableHolder; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellType; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.util.CellRangeAddress; import java.util.List; /** * 行合并工具类 * * @author DaiHaijiao */ public class ExcelFillCellMergeStrategyUtils implements CellWriteHandler { /** * 合并字段的下标 */ private int[] mergeColumnIndex; /** * 合并几行 */ private int mergeRowIndex; public ExcelFillCellMergeStrategyUtils(int mergeRowIndex, int[] mergeColumnIndex) { this.mergeRowIndex = mergeRowIndex; this.mergeColumnIndex = mergeColumnIndex; } @Override public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) { } @Override public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) { } @Override public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) { } @Override public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List list, Cell cell, Head head, Integer integer, Boolean aBoolean) { //当前行 int curRowIndex = cell.getRowIndex(); //当前列 int curColIndex = cell.getColumnIndex(); if (curRowIndex > mergeRowIndex) { for (int i = 0; i < mergeColumnIndex.length; i++) { if (curColIndex == mergeColumnIndex[i]) { this.mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex); break; } } } } private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) { //获取当前行的当前列的数据和上一行的当前列列数据，通过上一行数据是否相同进行合并 //获取当前行的第一列 Cell firstNowCell = cell.getSheet().getRow(curRowIndex).getCell(curColIndex); Object curData = firstNowCell.getCellTypeEnum() == CellType.STRING ? firstNowCell.getStringCellValue() : firstNowCell.getNumericCellValue(); Row preRow = cell.getSheet().getRow(curRowIndex - 1); if (preRow == null) { // 当获取不到上一行数据时，使用缓存sheet中数据 preRow = writeSheetHolder.getCachedSheet().getRow(curRowIndex - 1); } Cell preCell = preRow.getCell(curColIndex); Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue(); // 比较当前行的第一列的单元格与上一行是否相同，相同合并当前单元格与上一行 if (curData.equals(preData)) { Sheet sheet = writeSheetHolder.getSheet(); List mergeRegions = sheet.getMergedRegions(); boolean isMerged = false; for (int i = 0; i < mergeRegions.size() && !isMerged; i++) { CellRangeAddress cellRangeAddr = mergeRegions.get(i); // 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元 if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) { sheet.removeMergedRegion(i); cellRangeAddr.setLastRow(curRowIndex); sheet.addMergedRegion(cellRangeAddr); isMerged = true; } } // 若上一个单元格未被合并，则新增合并单元 if (!isMerged) { CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex); sheet.addMergedRegion(cellRangeAddress); } } } }

ExcelUtils.java

import com.alibaba.excel.EasyExcel; import com.alibaba.excel.ExcelWriter; import com.alibaba.excel.write.metadata.WriteSheet; import com.alibaba.excel.write.metadata.fill.FillConfig; import com.alibaba.excel.write.metadata.fill.FillWrapper; import org.apache.commons.lang3.StringUtils; import org.springframework.core.io.ClassPathResource; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.io.InputStream; import java.net.URLEncoder; import java.time.LocalDate; import java.time.ZoneId; import java.time.format.DateTimeFormatter; import java.util.*; /** * excel导出工具类 * */ public class ExcelUtils { /** * 导出数据到指定Excel * * @param response HttpServletResponse对象 * @param excelPath Excel模板地址 * @param excelFileName 文件名称 * @param outerMap 头部内容map * @param innerMapList 表格内容list * @param excelFillCellMergePrevColUtils 列合并参数 * @param excelFillCellMergeStrategyUtils 行合并参数 * @throws IOException 异常错误 */ public static void exportToTemplate(HttpServletResponse response, String excelPath, String excelFileName, Map outerMap, List innerMapList, ExcelFillCellMergePrevColUtils excelFillCellMergePrevColUtils, ExcelFillCellMergeStrategyUtils excelFillCellMergeStrategyUtils) throws IOException { InputStream inputStream = new ClassPathResource(excelPath).getInputStream(); response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); String fileName = URLEncoder.encode(excelFileName, "UTF-8"); response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx"); ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()) .withTemplate(inputStream) .registerWriteHandler(excelFillCellMergePrevColUtils) .registerWriteHandler(excelFillCellMergeStrategyUtils) .build(); WriteSheet writeSheet = EasyExcel.writerSheet().build(); FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build(); if (null != innerMapList && innerMapList.size() > 0) { FillWrapper listWrapper = new FillWrapper("list", innerMapList); excelWriter.fill(listWrapper, fillConfig, writeSheet); } if (null != outerMap && outerMap.size() > 0) { excelWriter.fill(outerMap, writeSheet); } excelWriter.finish(); } /** * 验证并获取excel单元格内的值 * * @param columnData 列值,object类型 * @param rowIndex 行号 * @param columnIndex 列号 * @param fieldName 字段名称 * @param lengthLimit 限制长度数（null时不做判断） * @param ifJudgeEmpty 是否需要判空（默认是） * @return 字符串格式值 * @throws Exception 逻辑异常 */ public static String checkValue(Object columnData, int rowIndex, int columnIndex, String fieldName, Integer lengthLimit, Boolean ifJudgeEmpty) throws Exception { String value = getStringValue(columnData); ifJudgeEmpty = null == ifJudgeEmpty ? true : ifJudgeEmpty; if (ifJudgeEmpty) { //需要判空 if (StringUtils.isEmpty(value)) { throw new Exception("第" + (rowIndex + 1) + "行，第" + (columnIndex + 1) + "列，" + fieldName + "不能为空"); } } if (null != lengthLimit && lengthLimit > 0) { //需要判断字符长度 if (StringUtils.isNotEmpty(value)) { if (value.length() > lengthLimit) { throw new Exception("第" + (rowIndex + 1) + "行，第" + (columnIndex + 1) + "列，" + fieldName + "不能超过" + lengthLimit + "个字符"); } } } return value; } /** * String => LocalDate * 入参str和pattern格式需要对应 * * @param str * @return LocalDate */ public static LocalDate str2LocalDate(String str) { if (StringUtils.isEmpty(str)) { return null; } if (str.indexOf("-") != -1 || str.indexOf("/") != -1) { String pattern = str.indexOf("/") != -1 ? "yyyy/MM/dd" : "yyyy-MM-dd"; try { //测试日期字符串是否符合日期 DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern(pattern); return LocalDate.parse(str, dateTimeFormatter); } catch (Exception e) { pattern = str.indexOf("/") != -1 ? "yyyy/M/d" : "yyyy-M-d"; DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern(pattern); return LocalDate.parse(str, dateTimeFormatter); } } else { Calendar calendar = new GregorianCalendar(1900, 0, -1); Date date = calendar.getTime(); int amount = Integer.parseInt(str); if (amount > 0) { Calendar calendar1 = Calendar.getInstance(); calendar1.setTime(date); calendar1.add(Calendar.DAY_OF_YEAR, amount); date = calendar.getTime(); } return date.toInstant().atZone(ZoneId.systemDefault()).toLocalDate(); } } /** * 获取String类型的值 * * @param columnData 列值,object类型 * @return 字符串格式值 */ public static String getStringValue(Object columnData) { if (columnData == null) { return null; } else { String res = columnData.toString().replace("[\\t\\n\\r]", "").trim(); return res; // //判断是否是科学计数法 true是科学计数法,false不是科学计数法 // boolean isMache=SCIENTIFIC_COUNTING_METHOD_PATTERN.matcher(res).matches(); // if(isMache){ // BigDecimal resDecimal = new BigDecimal(res); // return resDecimal.toPlainString(); // }else { // return res; // } } } }

上面的Excel中牵扯合并相关类，下一个帖子到时候会讲述合并相关用法。

4. 控制层用法

4.1 导出模板

/** * 导出模板 * * @param response HttpServletResponse对象 * @throws Exception 导出异常 */ @GetMapping(value = "/exportTemplate", produces = "application/octet-stream") public void exportTemplate(HttpServletResponse response) throws Exception { ExcelUtils.exportToTemplate(response, "excel/某某管理导入模板.xlsx", "某某管理导入模板", null, null, null, null); }

4.2 导出数据

接收导出数据的入参，可以单个单个的接收，也可以直接定义一个对象去接收，此处采用对象来接收的，如需对参数进行校验，可以通过注解的方式进行数据校验

UserExcelParam.java

import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; import java.io.Serializable; /** * 某某管理导出入参 * * @author DaiHaijiao */ @Data @NoArgsConstructor @AllArgsConstructor public class UserExcelParam implements Serializable { private static final long serialVersionUID = 0L; /** * 主键id */ private String dataIds; /** * 所属单位(组织机构表id) */ private String departmentId; /** * 人员名 */ private String userName; /** * 证书状态（-1已过期 0即将过期 1正常） */ private Integer status; } /** * 导出数据 * * @param excelParam 导出入参 * @param response HttpServletResponse对象 * @throws Exception 导出异常 */ @GetMapping(value = "/export", produces = "application/octet-stream") public void export(@RequestBody @Validated UserExcelParam excelParam, HttpServletResponse response) throws Exception { // excelParam为入参对象，也可拆成单个参数来接收 // 根据入参查询用户数据集合 List resultList = userService.list(excelParam); String title = "这个是Excel导出后Excel里面显示的标题"; Map outerMap = new HashMap(2); outerMap.put("title", title); List innerMapList = new ArrayList(); User item; String startDate, endDate; for (int i = 0; i < resultList.size(); ++i) { item = resultList.get(i); Map innerMap = new HashMap(16); innerMap.put("index", i + 1); innerMap.put("name", item.getName()); innerMap.put("departmentName", item.getDepartmentName()); innerMap.put("userName", item.getUserName()); // 注意：时间需要转换成字符串形式 startDate = DateUtils.localDate2String(item.getStartValidity(), "yyyy-MM-dd"); if (null == item.getEndValidity()) { innerMap.put("validityPeriod", startDate + " ~ "); } else { endDate = DateUtils.localDate2String(item.getEndValidity(), "yyyy-MM-dd"); innerMap.put("validityPeriod", startDate + " ~ " + endDate); } innerMap.put("someTypeName", item.getSomeTypeName()); innerMap.put("statusVal", item.getStatusVal()); innerMapList.add(innerMap); } ExcelUtils.exportToTemplate(response, "excel/某某管理导出模板.xlsx", title, outerMap, innerMapList, null, null); }

如果导出模板中字段太多，可以在上述代码的for循环中直接对象转map，对于个别特殊字段手动在处理一次。

4.3 导入数据

这个稍微有点麻烦，首先我们需要创建监听类。监听类中需要对导入的Excel中的每一条数据进行校验，当发现有数据异常时会抛出异常，终止后面的数据校验。数据全部校验完成后会产生一个数据集合，最后执行的批量插入。

UserReadExcelListener.java

import com.alibaba.excel.context.AnalysisContext; import com.alibaba.excel.event.AnalysisEventListener; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; import lombok.SneakyThrows; import lombok.extern.slf4j.Slf4j; import org.apache.commons.lang3.StringUtils; import java.time.LocalDate; import java.util.ArrayList; import java.util.LinkedHashMap; import java.util.List; /** * 某某管理数据导入监听 * * @author DaiHaijiao */ @Data @NoArgsConstructor @AllArgsConstructor @Slf4j public class UserReadExcelListener extends AnalysisEventListener { /** * 解析的数据集合 */ List dataList = new ArrayList(); /** * 资质类型字典数据 */ static List dictDataList; /** * 所属单位 */ static String departmentId; public static UserReadExcelListener newBean(List dictDatas, String deptId) { dictDataList = dictDatas; departmentId = deptId; return new UserReadExcelListener(); } /** * 读取excel每一行都会触发本方法 * * @param data 行数据 * @param context AnalysisContext */ @SneakyThrows @Override public void invoke(Object data, AnalysisContext context) { User excelData = new User(); // 当前数据行号,从0开始 Integer rowIndex = context.readRowHolder().getRowIndex(); LinkedHashMap dataTemp = (LinkedHashMap) data; for (int i = 0; i < dataTemp.size(); i++) { this.parseColumnData(rowIndex, i, dataTemp.get(i), excelData); } // 解析完一行数据后,添加到集合中 excelData.setDepartmentId(departmentId); dataList.add(excelData); } /** * 解析列值 * * @param rowIndex 行号 * @param columnIndex 列号 * @param columnData 列值,object类型 * @param excelData 实例对象 * @throws Exception 逻辑异常 */ private void parseColumnData(Integer rowIndex, Integer columnIndex, Object columnData, User excelData) throws Exception { // 逐列判断并使用正确的类型接收value,列号从0开始 if (columnIndex == 1) { //某编号 String value = ExcelUtils.checkValue(columnData, rowIndex, columnIndex, "某编号", 16, null); excelData.setNo(value); } else if (columnIndex == 2) { //某类型 String value = ExcelUtils.checkValue(columnData, rowIndex, columnIndex, "某类型", null, null); DictData dictData = dictDataList.stream().filter(item -> value.equals(item.getDictLabel())).findFirst().orElse(null); if (null == dictData) { throw new Exception("第" + (rowIndex + 1) + "行，第" + (columnIndex + 1) + "列，某类型有误"); } excelData.setSomeType(dictData.getDataId()); } else if (columnIndex == 3) { //某单位 excelData.setIssuingUnit(ExcelUtils.checkValue(columnData, rowIndex, columnIndex, "某单位", 64, false)); } else if (columnIndex == 4) { //有效期之开始时间 String value = ExcelUtils.checkValue(columnData, rowIndex, columnIndex, "有效期之开始时间", null, null); try { excelData.setStartValidity(ExcelUtils.str2LocalDate(value)); } catch (Exception e) { throw new Exception("第" + (rowIndex + 1) + "行，第" + (columnIndex + 1) + "列，有效期之开始时间格式有误"); } } else if (columnIndex == 5) { //有效期之结束时间 String value = ExcelUtils.checkValue(columnData, rowIndex, columnIndex, "有效期之结束时间", null, false); LocalDate endValidity = null; if (StringUtils.isNotEmpty(value)) { try { endValidity = ExcelUtils.str2LocalDate(value); } catch (Exception e) { throw new Exception("第" + (rowIndex + 1) + "行，第" + (columnIndex + 1) + "列，有效期之结束时间格式有误"); } long start = DateUtils.localDate2Date(excelData.getStartValidity()).getTime(); if (DateUtils.localDate2Date(endValidity).getTime()

【本文地址】

公司简介

联系我们